{template('../public/header.html')}
<!-- -->
<div class="subnav">
    <h1 class="title_2 line_x">在线执行SQL语句</h1>
</div>
<div class="common-form pad_10">

<!-- b -->

<STYLE type="text/css">
<!--
*{word-wrap:break-word;outline:none}
html,body{height:100%;}
body{ overflow-x:auto; }
body,td,th{font-size: 12px;color:#333333;font-family:"宋体";margin:0;font:12px "Lucida Grande", Verdana, Lucida, Helvetica, Arial, "宋体", sans-serif}
input{ background:#F9F9F9;color:#555;font:12px "Lucida Grande", Verdana, Lucida, Helvetica, Arial, "宋体", sans-serif}
ul,li,div{ margin:0px; padding:0px;}
li{height:30px; line-height:30px;}
textarea, input, select{padding:2px;border:1px solid;border-color:#666 #ccc #ccc #666;background:#F9F9F9;color:#333}
textarea:hover, textarea:focus{border-color:#09C;background:#F5F9FD}
a:link{text-decoration:none; font-size:12px; font-style:normal; color:#3F628C;}
a:visited{text-decoration:none; font-size:12px; font-style:normal; color:#3F628C;}
a:hover{text-decoration: underline; font-size:12px; font-style:normal; color:#3F628C;}
a:active{text-decoration:none; font-size:12px; font-style:normal; color:#3F628C;}
td{ color:#333333;}
img{ border:0px;}
li{ list-style:none;}

.line{ border-right:1px solid #3FB3E3; height:100%; margin-top:1px;  width:10px;}
.right{ background:#9FD7FF; height:100%; border-left:1px solid red; }
.r_main{ background-color:#9FD7FF; width:100%;  margin-left:-1px; }	
.r_content{ background:#FFFFFF; clear:left; font-size:12px; width:100%; height:100%; overflow-x:auto; }
.r_content_1{ background:#FFFFFF; width:100%; BACKGROUND-COLOR: transparent; overflow-x:auto; }
.page{ height:25px; line-height:25px;text-align:right; border-top:1px solid #DEEFFA; width:98%; margin:0 auto; margin-top:10px;}
.td_title{ height:30px; background: #F9FBFD; border-top:4px solid #DEEFFA; border-bottom:1px solid #DEEFFA; border-right:1px inset #dedede; border-right:1px inset #dedede; color:#008EBD; font-size:12px; font-weight:bold;}				
.td_border{  border-bottom:1px solid #DEEFFA; border-right:1px inset #dedede; }
.tb_style{ font-size:12px; width:98%; margin:0 auto; margin-top:10px; margin-left:10px; margin-right:10px; /* TABLE-LAYOUT: fixed */ }
.tb_style td { /*word-wrap:break-word; word-break:break-all; */ }
.tr_mouseover{ background-color:#EEF8EE; }
.tr_mouseout{ background-color:#ffffff; }
.tr_select{ background-color:#FBFEFF; }
/*
.rb1{BORDER-RIGHT: #002D96 1px solid; PADDING-RIGHT: 2px; BORDER-TOP: #002D96 1px solid; PADDING-LEFT: 2px; FONT-SIZE: 12px; FILTER: progid:DXImageTransform.Microsoft.Gradient(GradientType=0, StartColorStr=#FFFFFF, EndColorStr=#9DBCEA); BORDER-LEFT: #002D96 1px solid; CURSOR: hand; COLOR: black; PADDING-TOP: 2px; BORDER-BOTTOM: #002D96 1px solid;color:#033d61;}
*/
.rb1{margin:3px 0;padding:2px 5px;*padding:4px 5px 1px;border-color:#ddd #666 #666 #ddd;background:#DDD;color:#000;cursor:pointer;vertical-align:middle}
.rb2{margin:3px 0;padding:2px 5px;*padding:4px 5px 1px;border-color:#ddd #666 #666 #ddd;cursor:pointer;vertical-align:middle}

/*......确认框......*/

#alert{width:340px; height:165px; position:absolute; top:100px; left:200px; border:1px solid #1F385D; background:#FFFFFF; z-index:99;}
#alert_title{width:100%;height:30px; line-height:30px; background:url(alert_title.jpg); font-size:12px; color:#FFFFFF;}
#alert_content{height:104px; width:100%; font-size:12px; BACKGROUND: url(alert_mainbg.jpg); margin-top:-1px; }
#alert_content_1{height:104px; float:left; width:80px; text-align:center;}
#alert_content_2{height:82px; width:256px;  font-size:12px; float:left;  margin-top:15px;line-height:20px;}
#alert_content_3{width:100%;  text-align:right; background:url(alert_bottom.jpg); height:31px;  }
-->
</STYLE>
<script language="javascript">
	function ShowHelp(str_type){
		var myinput=document.forms[0].sql;
		if(str_type=="select"){
			myinput.value = "SELECT [字段1], [字段2], [字段3] AS [temp] FROM [表名]";
		}else if(str_type=="addc"){
			myinput.value = "ALTER TABLE [表名] ADD [字段名] VARCHAR(20) NULL";
		}else if(str_type=="renamec"){
			myinput.value="EXEC sp_rename '[表名].[字段名]', '新字段名', 'COLUMN'";
		}else if(str_type=="dropc"){
			myinput.value="ALTER TABLE [表名] DROP COLUMN [字段名]";
		}else if(str_type=="addt"){
			myinput.value="CREATE TABLE [表名] ( [字段1] INT, [字段2] VARCHAR(20) NULL)";
		}else if(str_type=="renamet"){
			myinput.value="EXEC sp_rename '旧表名', '新表名'";
		}else if(str_type=="dropt"){
			myinput.value="DROP TABLE pubdata.dbo.authors";
		}else if(str_type=="copy"){
			myinput.value="select * into tb_new from tb";
		}else if(str_type=="repeat"){
			myinput.value="select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1"
		}else if(str_type=="setcw"){
			myinput.value="ALTER TABLE [表名] ALTER COLUMN [字段名] VARCHAR(255) NULL"
		}else if(str_type=="copytb"){
			myinput.value="INSERT mytable (column1,column2) SELECT field1,field2 FROM table2 WHERE id>0"
		}
	}

	$("#execu").click(function(){
		var sql=$("#sql").val();
		if (sql=="") {
			$("#sql").css("border","1px solid red");
			return false;
			}
		});
	var rscount = 0;

    function chk_idtr(id) {
        var objc = document.getElementById("chk" + id); //多选框
        var obju = document.getElementById("uhk" + id); //tr
        if (objc.checked == '') {
            objc.checked = 'checked';
            //obju.className = 'listselect';
            obju.style.backgroundColor = '#D2E3F2';
        } else {
        objc.checked = '';
            //obju.className = 'listmouseout';
            //obju.style.backgroundColor = '#FFFFFF';
            obju.style.backgroundColor = '';
        }
    }
</script>

<div class="r_main">
      <div class="r_content">
          <div class="r_content_1">

			<div class="container" id="cpcontainer">
			<form  method="post" action="{:U('sql/index?act=exec')}" name="form1">
			<table class="tb_style" width="98%" align="center"> 
				<tr>
					<td class="td_title">SQL语句执行操作&nbsp;&nbsp;<span style="color:#009966;font-weight:normal;">[&nbsp;示例：&nbsp;
						<a href="javascript:void(0)" onclick="ShowHelp('select');">查询数据</a>
						<a href="javascript:void(0)" onclick="ShowHelp('addc');">新增字段</a>
						<a href="javascript:void(0)" onclick="ShowHelp('renamec');">重命名字段</a>
						<a href="javascript:void(0)" onclick="ShowHelp('dropc');">删除字段</a>
						<a href="javascript:void(0)" onclick="ShowHelp('addt');">创建数据表</a>
						<a href="javascript:void(0)" onclick="ShowHelp('renamet');">重命名表</a>
						<a href="javascript:void(0)" onclick="ShowHelp('dropt');">删除数据表</a>
						<a href="javascript:void(0)" onclick="ShowHelp('copy')">复制带数据的表</a>
						<a href="javascript:void(0)" onclick="ShowHelp('repeat');">查找重复数据</a>
						<a href="javascript:void(0)" onclick="ShowHelp('setcw');">修改列宽</a>
						<a href="javascript:void(0)" onclick="ShowHelp('copytb');">异表拷贝</a>
						&nbsp;]</span> ( <span style="font-weight:normal;"><font color=red>#@</font> 可代替数据表前缀 </span>)
					</td>
				  </tr>
				<tr>
					<td class="td_border" align="center">
					 <textarea  name="sql" id="sql" cols="120" rows="8" ></textarea>
					</td>
				  </tr>
				  <tr>  
					<td class="td_border" align="center">
					 <input type="submit" class="rb1" id="execu" value="执行ＳＱＬ"> 
					&nbsp;&nbsp;   <input type="button" value="清除记录" class="rb1" onclick="javascript:window.location='{:U('sql/index')}';">
					</td>
				</tr>
				<tr>
					<td class="td_border" align="left"><span id="SQLStr"></span><span id="RsCount" style="margin-left:15px;color:#9BB4C8"></span></td>
				</tr>
			</table>
			</form></div>

			{asp} Dim i_affected, is_multi : is_multi = False : i_affected = 0 {/asp}
			{if app.req("act")="exec"}
				{asp executeResult()}
			{/if}
		</div>
	</div>
</div>

{if app.req("act")="exec"}
	{if Instr(sql,"[go]")>0}
	{asp}
		is_multi = True
		exeuteSqlMulti()
	{/asp}
	{/if}
{/if}

{asp}
	'执行Sql多条语句操作
	Sub exeuteSqlMulti()
		Dim o_conn, i, k, sqlarr, jstr, jarr, i_errNum
		sqlarr = Split(sql,"[go]")
		jstr = Replace(sql,"'","\'")
		jstr = Replace(jstr,VbTab," ")
		jstr = AB.D.NsSpace(jstr)
		jstr = Replace(jstr,"""","\""")
		jarr = Split(jstr,"[go]")
		Dim SQLNum : SQLNum = Ubound(sqlarr)
		Err.Clear
		On Error Resume Next
		i_affected = 0
		i_errNum = 0
		If SQLNum >=0 then
			Set o_conn = App.Dao.db.Conn
			k = 0
			o_conn.BeginTrans
			For i=0 to SQLNum
				sqlarr(i) = Trim(sqlarr(i))
				If sqlarr(i)<>"" Then
					k = k + 1
					o_conn.Execute(sqlarr(i))
					If Err.Number<>0 Then
						i_errNum = i_errNum + 1
						Err.Clear
						App.Out.Echo("<script language=javascript>AddTag(""<font style='color:green; font-size:12px;'>第"&(i+1)&"条 SQL语句: </font><font style='color:#034670; font-size:12px;'>"& jarr(i) &"</font> <font style='color:red; font-size:12px;'>有误->执行失败!</font>"");</script>" & VbCrlf)
						'App.Out.Echo("<font style='color:red; font-size: 12px;'>第"&k&"条 SQL语句: "& sqlarr(i) &" 有误->执行失败!</font><BR>" & VbCrlf)
						App.Out.Flush
					Else
						App.Out.Echo("<script language=javascript>AddTag('<font style=""color:green; font-size:12px;"">第"&(i+1)&"条 SQL语句: </font><font style=""color:#034670; font-size:12px;"">"& jarr(i) &"</font> <font style=""color:blue; font-size:12px;"">正确->执行成功!</font>');</script>" & VbCrlf)
						'App.Out.Echo("<font style='color:green; font-size: 12px;'>第"&(k)&"条 SQL语句: "& sqlarr(i) &" 正确->执行成功!</font><BR>" & VbCrlf)
						App.Out.Flush
					End If
				End If
			Next
			If i_errNum>0 Then o_conn.Rollback
			o_conn.CommitTrans
		End If
	End Sub
{/asp}
{asp}
	Sub executeResult
		Dim isSelect,resultRs,errorFlag,errObj,fieldObj,i, exeResultNum
		Dim n:n=0
		Dim o_db : Set o_db = App.Dao.New.db
{/asp}
{if trim(sql)<>""}
	{asp}
		isSelect = (lcase(left(trim(sql),6)) = "select")
		On Error Resume Next
		IF isSelect = True Then
			Set resultRs = o_db.Conn.execute(sql,exeResultNum)
		Else
			If Instr(sql,"[go]")<=0 Then
				o_db.Conn.execute sql,exeResultNum
			Else
				is_multi = True
			End If
		End If
		IF o_db.Conn.Errors.count<>0 Then errorFlag = true : set resultRs = o_db.Conn.Errors Else errorFlag = False
		Dim arrRecord,tempRs
		IF isSelect = True Then
			'Set tempRs = o_db.Run(sql,"exe")
			Set tempRs = o_db.Conn.Execute(sql)
			arrRecord = tempRs.GetRows()
			Set tempRs = Nothing
		End If
	{/asp}
	{if errorFlag}
		<table class="tb_style" cellpadding="0" cellspacing="0" border="0" width="98%" align="center">
		<tr>
			<td class="td_title">&nbsp;错误号</td>
			<td class="td_title"> 来源</td>
			<td class="td_title"> 描述</td>
			<td class="td_title">帮助</td>
			<td class="td_title"> 帮助文档</td> 
		</tr>
		{asp}
				For i=1 To o_db.Conn.Errors.count
					Set errObj = o_db.Conn.Errors(i-1)
		{/asp}
		<tr onmouseover="this.className='tr_mouseover'" onmouseout="this.className='tr_mouseout'" onclick="chk_idtr('{$i}')" id='uhk{$i}' id='uhk{$i}' class="tr_mouseout">
			<td class="td_border">{:errObj.Number}<input type='checkbox' id='chk{$i}' style='display:none' value='{$i}' onclick="chk_idtr('{$i}')"></td>
			<td class="td_border">{:errObj.Description}</td>
			<td class="td_border">{:errObj.Source}</td>
			<td class="td_border">{:errObj.Helpcontext}</td>
			<td class="td_border">{:errObj.HelpFile}</td>
		</tr>
		{asp Next}
		</table>
	{else}
		{if isArray(arrRecord)}
			<script type="text/javascript">rscount = {:Ubound(arrRecord,2)+1};</script>
		{/if}
		<table class="tb_style" border="0" cellpadding="0" cellspacing="0" width="98%" align="center">
		{if isSelect = True}
			<tr>
				<td  class="td_title" height="30" nowrap>&nbsp;序号</td>
				{asp For Each fieldObj In resultRs.Fields}
				<td  class="td_title" height="30" nowrap>&nbsp;{:fieldObj.name}</td>
				{asp next}
			</tr>
			{asp}
				Do while Not resultRs.Eof
					n=n+1 : IF n>100 Then Exit Do
			{/asp}
			<tr onmouseover="this.className='tr_mouseover'" onmouseout="this.className='tr_mouseout'" onclick="chk_idtr('{$n}')" id='uhk{$n}' id='uhk{$n}' class="tr_mouseout">
				<td class="td_border" style="color:#00f;" height="25" width="35" nowrap>&nbsp;[{$n}]<input type='checkbox' id='chk{$n}' style='display:none' value='{$n}' onclick="chk_idtr('{$n}')"></td>
				{asp}
					For Each fieldObj In resultRs.Fields
						Dim MaxLg:IF resultRs.Fields.Count>1 Then MaxLg=60 Else MaxLg=40
				{/asp}
				<td class="td_border" nowrap height="25" 
					title="{:Replace(Replace(Server.HTMLEncode(fieldObj.value),VBCrlf,"&#13;"),"""","&quot")}">&nbsp;
				{asp}
					IF  AB.C.IsNul(fieldObj.value) Then 
						AB.C.Print "&nbsp;"
					Else
						IF Len(fieldObj.value)>MaxLg Then
							'AB.C.Print Server.HTMLEncode(left(fieldObj.value,MaxLg))&"..."
							AB.C.Print AB.C.RP(left(fieldObj.value,MaxLg),Array("<",">"),Array("&lt;","&gt;"))&"..."
						Else
							'AB.C.Print Server.HTMLEncode(fieldObj.value)
							AB.C.Print AB.C.RP(fieldObj.value,Array("<",">"),Array("&lt;","&gt;"))
						End IF
					End IF
				{/asp}
				</td>
				{asp Next}
			</tr>
			{asp}
					resultRs.MoveNext
				Loop
			{/asp}
		{else}
				<tr> <td class="td_title" height="30">&nbsp;执行结果</td></tr>
				<tr>
					<td class="td_border" height="25">
						<div id="execResult" width="99%" style="font-size; 12px;">{if not is_multi}&nbsp; {:exeResultNum & "条纪录被影响"}{/if}</div>
					</td>
				</tr>
		{/if}
		</table>
	{/if}
{/if}
	{asp Set resultRs = Nothing}
	<div style="height:10px;display:block"></div>
	{if trim(sql)<>""}
		<SCRIPT type="text/javascript">
			function AddTag(str){
				var tag;
				tag = document.createElement("li");
				tag.innerHTML=str;
				document.getElementById('execResult').appendChild(tag);
			}
			{if not is_multi}
			document.getElementById("SQLStr").innerHTML='<font color=green>SQL语句&gt;&gt; </font>&nbsp;&nbsp;<font color=red>{:Replace(sql,"'","\'")}</font>';
			document.getElementById("sql").value=''+'{:Replace(Trim((Replace(Trim(Request.Form("sql")),VBCrlf," \n"))),"'","\'")}';
			if(rscount >= 1) { 
				if(rscount<=100)
					document.getElementById("RsCount").innerHTML='[共<font color=\"#718EBD\">'+rscount+'</font>条记录]';
				else
					document.getElementById("RsCount").innerHTML='[前<font color=\"#718EBD\">100</font>条/共<font color=\"#718EBD\">'+rscount+'</font>条记录]';
			}
			{/if}
		</SCRIPT>
	{/if}
{asp end sub}

<!-- e -->

</div>
</div>
{template('../public/footer.html')}
</body>
</html>